package no.ntnu.fp.storage;

import java.sql.*;


public class CreateDB {
	
	public static void main(String[] args) {
		create();
		
			
	}
	
	public static void create() {
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
		}
		catch (ClassNotFoundException cnfe) {
			System.out.println(cnfe.getCause());
		}
		catch (IllegalAccessException iae) {
			System.out.println(iae.getCause());
		}
		catch (InstantiationException ie) {
			System.out.println(ie.getCause());
		}
		try {
			Connection conn = DriverManager.getConnection("jdbc:derby:fp46db;create=true");
			Statement ct = conn.createStatement();
			ct.executeUpdate("CREATE TABLE meetingroom (roomno VARCHAR(6) NOT NULL CONSTRAINT room_pkey PRIMARY KEY)");
			ct.executeUpdate("CREATE TABLE employee (eid BIGINT NOT NULL CONSTRAINT emp_pkey PRIMARY KEY, username VARCHAR(20) NOT NULL, password VARCHAR(20) NOT NULL, name VARCHAR(40), email VARCHAR(25) NOT NULL, birthdate CHAR(10), CONSTRAINT emp_unique UNIQUE(username, email))");
			ct.executeUpdate("CREATE TABLE appointment (aid BIGINT NOT NULL CONSTRAINT app_pkey PRIMARY KEY, eid BIGINT NOT NULL, date CHAR(10), starttime TIME, endtime TIME, description VARCHAR(50), place VARCHAR(20), type CHAR(1) DEFAULT 'a', CONSTRAINT app_fkey_eid FOREIGN KEY(eid) REFERENCES employee(eid) ON DELETE CASCADE)");
			ct.executeUpdate("CREATE TABLE participant (mid BIGINT NOT NULL, eid BIGINT NOT NULL, status VARCHAR(10) NOT NULL, aid BIGINT NOT NULL, CONSTRAINT part_pkey PRIMARY KEY (mid, eid), CONSTRAINT part_fkey_mid FOREIGN KEY (mid) REFERENCES appointment(aid) ON DELETE CASCADE, CONSTRAINT part_fkey_eid FOREIGN KEY (eid) REFERENCES employee(eid) ON DELETE CASCADE)");
			ct.executeUpdate("CREATE TABLE notification (nid BIGINT NOT NULL CONSTRAINT ntf_pkey PRIMARY KEY, eid BIGINT NOT NULL, mid BIGINT NOT NULL, type VARCHAR(30) NOT NULL, message VARCHAR(50), CONSTRAINT ntf_fkey_eid FOREIGN KEY (eid) REFERENCES employee(eid) ON DELETE CASCADE)");
			ct.close();
			conn.close();
			DriverManager.getConnection("jdbc:derby:fp46db;shutdown=true");
		}
		catch (SQLException sqle) {
			System.out.println(sqle.getCause());
		}
	}
}